--create tables


DROP TABLE user_role
go

CREATE TABLE user_role (
       [user_id]              varchar(50) NOT NULL,
       role_id              varchar(50) NOT NULL
)
go

CREATE UNIQUE INDEX IX_user_role ON user_role
(
       [user_id]                        ASC,
       role_id                        ASC
)
go


DROP TABLE [user]
go

CREATE TABLE [user] (
       id                   varchar(50) NOT NULL,
       name                 varchar(100) NOT NULL,
       description          varchar(200) NULL,
       password             varchar(20) NULL,
       auth_type            varchar(50) NOT NULL
)
go


ALTER TABLE [user]
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE role
go

CREATE TABLE role (
       id                   varchar(50) NOT NULL,
       name                 varchar(100) NOT NULL,
       description          varchar(200) NULL
)
go


ALTER TABLE role
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE plugins
go

CREATE TABLE plugins (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NOT NULL,
       data                 image NULL,
       [desc]                 varchar(200) NULL,
       version              varchar(30) NULL
)
go


ALTER TABLE plugins
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE module
go

CREATE TABLE module (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NOT NULL,
       plugin_id            varchar(50) NULL,
       param                varchar(2000) NULL,
       init_string          varchar(2000) NULL
)
go


ALTER TABLE module
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE [log]
go

CREATE TABLE [log] (
       id                   varchar(50) NOT NULL,
       datetime             datetime NOT NULL,
       computer_name        nvarchar(50) NULL,
       [user_id]              nvarchar(50) NOT NULL,
       source               nvarchar(200) NOT NULL,
       type                 int NULL,
       category             nvarchar(50) NULL,
       action               nvarchar(50) NOT NULL,
       comment              nvarchar(500) NULL
)
go


ALTER TABLE [log]
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE globalvariables
go

CREATE TABLE globalvariables (
       [key]                  varchar(200) NOT NULL,
       value                text NULL
)
go


ALTER TABLE globalvariables
       ADD PRIMARY KEY ([key] ASC)
go


DROP TABLE function_type
go

CREATE TABLE function_type (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NULL
)
go


ALTER TABLE function_type
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE function_module
go

CREATE TABLE function_module (
       function_id          varchar(50) NOT NULL,
       module_id            varchar(50) NOT NULL
)
go


ALTER TABLE function_module
       ADD PRIMARY KEY (function_id ASC)
go


DROP TABLE function_authorization
go

CREATE TABLE function_authorization (
       id                   varchar(50) NOT NULL,
       function_id          varchar(50) NULL,
       role_id              varchar(50) NULL
)
go


ALTER TABLE function_authorization
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE [function]
go

CREATE TABLE [function] (
       id                   varchar(50) NOT NULL,
       name                 varchar(500) NOT NULL,
       type_id              varchar(50) NULL,
       parent_id            varchar(50) NULL
)
go


ALTER TABLE [function]
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE clientconfig
go

CREATE TABLE clientconfig (
       id                   char(50) NOT NULL,
       host                 char(30) NOT NULL,
       module               char(100) NOT NULL,
       content              ntext NULL
)
go

CREATE UNIQUE INDEX IX_clientconfig2 ON clientconfig
(
       host                           ASC,
       module                         ASC
)
go


ALTER TABLE clientconfig
       ADD PRIMARY KEY (id ASC)
go



-- insert initial data

--Table Module
insert into [module](id,name,plugin_id,param,init_string) values('FunctionMgr','FunctionMgr','FunctionMgr','','')
go

insert into [module](id,name,plugin_id,param,init_string) values('ModuleMgr','ModuleMgr','ModuleMgr','','')
go

insert into [module](id,name,plugin_id,param,init_string) values('PluginMgr','PluginMgr','PluginMgr','','')
go

insert into [module](id,name,plugin_id,param,init_string) values('RoleMgr','RoleMgr','RoleMgr','','')
go

insert into [module](id,name,plugin_id,param,init_string) values('UserMgr','UserMgr','UserMgr','','')
go


--Table Function
insert into [function](id,name,type_id,parent_id) values('\','\','normal',null)
go

insert into [function](id,name,type_id,parent_id) values('\\System','System','normal','\')
go

insert into [function](id,name,type_id,parent_id) values('\\System\FunctionMgr','Function Manager','module','\\System')
go

insert into [function](id,name,type_id,parent_id) values('\\System\ModuleMgr','Module Manager','module','\\System')
go

insert into [function](id,name,type_id,parent_id) values('\\System\PluginMgr','Plugin Manager','module','\\System')
go

insert into [function](id,name,type_id,parent_id) values('\\System\RoleMgr','Role Manager','module','\\System')
go

insert into [function](id,name,type_id,parent_id) values('\\System\UserMgr','User Manager','module','\\System')			
go


--Table Function_Module
insert into function_module(function_id,module_id) values('\\System\FunctionMgr','FunctionMgr')
go

insert into function_module(function_id,module_id) values('\\System\ModuleMgr','ModuleMgr')
go

insert into function_module(function_id,module_id) values('\\System\PluginMgr','PluginMgr')
go

insert into function_module(function_id,module_id) values('\\System\RoleMgr','RoleMgr')
go

insert into function_module(function_id,module_id) values('\\System\UserMgr','UserMgr')
go


--Table User
insert into [user](id,name,description,password,auth_type) values('admin','admin','','admin','local')
go

--Table Role
go

insert into role(id,name,description) values('admins','admins','')
go

--Table user_role
insert into user_role(user_id,role_id) values('admin','admins')
go

--Table Function_Authorization
insert into function_authorization(id,function_id,role_id) values('1','\\System','admins')
go

insert into function_authorization(id,function_id,role_id) values('2','\\System\FunctionMgr','admins')
go

insert into function_authorization(id,function_id,role_id) values('3','\\System\ModuleMgr','admins')
go

insert into function_authorization(id,function_id,role_id) values('4','\\System\PluginMgr','admins')
go

insert into function_authorization(id,function_id,role_id) values('5','\\System\RoleMgr','admins')
go

insert into function_authorization(id,function_id,role_id) values('6','\\System\UserMgr','admins')
go

insert into globalvariables values('startup.plugin_ids','FunctionBrowser')
go